package com.xbongbong.paas.service.impl;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.xbongbong.i18n.util.I18nMessageUtil;
import com.xbongbong.paas.domain.entity.ext.PaasFormDataEntityExt;
import com.xbongbong.paas.enums.ComboTypeEnum;
import com.xbongbong.paas.enums.DownloadExcelTypeEnum;
import com.xbongbong.paas.enums.FieldTypeEnum;
import com.xbongbong.paas.enums.ImportExampleNameEnum;
import com.xbongbong.paas.enums.VisibleTypeEnum;
import com.xbongbong.paas.help.FileImportExportHelp;
import com.xbongbong.paas.pojo.FieldAttrEntityForImport;
import com.xbongbong.paas.pojo.dto.FormDataListDTO;
import com.xbongbong.paas.pojo.vo.ImportPoJo;
import com.xbongbong.paas.service.ExcelOperatorService;
import com.xbongbong.paas.toolbox.exception.XbbException;
import com.xbongbong.paas.toolbox.util.CollectionsUtil;
import com.xbongbong.paas.toolbox.util.RegexUtil;
import com.xbongbong.paas.toolbox.util.StringUtil;
import com.xbongbong.paas.util.ExcelUtil;
import com.xbongbong.pro.constant.I18nStringConstant;
import com.xbongbong.pro.enums.errorcodes.SystemErrorCodeEnum;
import com.xbongbong.saas.constant.BasicConstant;
import com.xbongbong.saas.constant.StringConstant;
import com.xbongbong.saas.constant.export.ImportConstant;
import com.xbongbong.saas.enums.ShowTypeEnum;
import com.xbongbong.saas.enums.XbbRefTypeEnum;
import com.xbongbong.saas.enums.business.PayPlanEnum;
import com.xbongbong.saas.enums.business.PaymentEnum;
import com.xbongbong.saas.enums.business.SupplierEnum;
import com.xbongbong.saas.enums.dictionary.PayPlanStatusEnum;
import com.xbongbong.saas.enums.dictionary.PaymentStatusEnum;
import com.xbongbong.saas.help.ImportHelper;
import com.xbongbong.saas.toolbox.help.FastJsonHelper;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Name;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheetDimension;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.stream.Collectors;

/**
 * Excel操作实现类
 *
 * @author wufeng
 * @date 2018/9/25 10:14
 */
@Service("excelOperatorService")
public class ExcelOperatorServiceImpl implements ExcelOperatorService {

    private static final Logger LOG = LoggerFactory.getLogger(ExcelOperatorServiceImpl.class);

    @Resource
    private ImportHelper importHelper;
    @Resource
    private FileImportExportHelp fileImportExportHelp;

    @Override
    public Workbook createExcelTemplate(Integer businessType, JSONArray dataExplain, String name, int excelType, int dataRowNum, boolean isFileImport) {
        Workbook templateWorkBook = null;
        try {
            List<FieldAttrEntityForImport> fieldAttrEntityForImportList = JSON.parseArray(JSON.toJSONString(dataExplain), FieldAttrEntityForImport.class);
            int titleLength = importHelper.hasSubForm(fieldAttrEntityForImportList);
            int headRowNum = titleLength + ImportConstant.VALUE_STAR_ROW_INDEX;
            XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
            XSSFSheet xssfSheet = xssfWorkbook.createSheet();
            CTSheetDimension dimension = xssfSheet.getCTWorksheet().getDimension();
            dimension.setRef("A1:A" + (headRowNum + dataRowNum));
            SXSSFWorkbook wb = new SXSSFWorkbook(xssfWorkbook, 500);
            SXSSFSheet sheet = wb.getSheetAt(0);
            name += I18nMessageUtil.getMessage(I18nStringConstant.EXCEL_NAME) + headRowNum;
            ExcelUtil.setTitle(wb, sheet, name);

            // 文本格式
            CellStyle textStyle = wb.createCellStyle();
            DataFormat dataFormat = wb.createDataFormat();
            textStyle.setDataFormat(dataFormat.getFormat("@"));

            int rowNum = 1;
            // 创建第二行 Excel表头
            SXSSFRow headRow = sheet.createRow(rowNum);
            // 如果有子表单的话，为其赋值
            SXSSFRow subHeadRow = null;
            // 二级子表单
            SXSSFRow subHeadRow2 = null;
            // 三级子表单
            SXSSFRow subHeadRow3 = null;
            int cellInt = 0;
            List<String> comboTextList;
            // 不是模板下载的情况，第一列增加一列其他信息
            DownloadExcelTypeEnum downloadExcelTypeEnum = DownloadExcelTypeEnum.getByType(excelType);
            if (downloadExcelTypeEnum.equals(DownloadExcelTypeEnum.ERROR)) {
                JSONObject dataObj = new JSONObject();
                dataObj.put("fieldType",FieldTypeEnum.TEXT.getType());
                dataObj.put("attrName",downloadExcelTypeEnum.getMsg());
                dataExplain.add(0,dataObj);
            }
            List<ImportPoJo> importPoJoList = new ArrayList<>();
            int j = 1;
            for (Object object : dataExplain) {
                JSONObject explainObject = (JSONObject) object;
                Integer fieldType = explainObject.getInteger("fieldType");
                String attrName = explainObject.getString("attrName");
                Integer comboType = explainObject.getInteger("comboType");
                // 子表单
                String[] options;
                if (Objects.equals(fieldType, FieldTypeEnum.SUB_FORM.getType())) {
                    if (subHeadRow == null) {
                        subHeadRow = sheet.createRow(++rowNum);
                    }
                    ImportPoJo first = new ImportPoJo();
                    first.setStart(cellInt);
                    int subCellInt = cellInt;
                    SXSSFCell subFormCell = headRow.createCell(cellInt);
                    ExcelUtil.setSubFormTitle(wb, subFormCell);
                    subFormCell.setCellValue(attrName);

                    JSONArray subJsonArray = explainObject.getJSONObject("subForm").getJSONArray("items");
                    List<ImportPoJo> secondList = new ArrayList<>();
                    for (Object subObject : subJsonArray) {
                        explainObject = (JSONObject) subObject;
                        attrName = explainObject.getString("attrName");
                        fieldType = explainObject.getInteger("fieldType");
                        comboType = explainObject.getInteger("comboType");
                        if (Objects.equals(fieldType, FieldTypeEnum.SUB_FORM.getType())) {
                            if (subHeadRow2 == null) {
                                subHeadRow2 = sheet.createRow(++rowNum);
                            }
                            ImportPoJo second = new ImportPoJo();
                            second.setStart(subCellInt);
                            int subCellInt2 = subCellInt;
                            SXSSFCell subFormCell2 = subHeadRow.createCell(subCellInt);
                            ExcelUtil.setSubFormTitle(wb, subFormCell2);
                            subFormCell2.setCellValue(attrName);
                            JSONArray subJsonArray2 = explainObject.getJSONObject("subForm").getJSONArray("items");
                            List<ImportPoJo> thirdList = new ArrayList<>();
                            for (Object subObject2 : subJsonArray2) {
                                explainObject = (JSONObject) subObject2;
                                attrName = explainObject.getString("attrName");
                                fieldType = explainObject.getInteger("fieldType");
                                comboType = explainObject.getInteger("comboType");
                                if (Objects.equals(fieldType, FieldTypeEnum.SUB_FORM.getType())) {
                                    if (subHeadRow3 == null) {
                                        subHeadRow3 = sheet.createRow(++rowNum);
                                    }
                                    ImportPoJo third = new ImportPoJo();
                                    third.setStart(subCellInt2);
                                    int subCellInt3 = subCellInt2;
                                    SXSSFCell subFormCell3 = subHeadRow2.createCell(subCellInt2);
                                    ExcelUtil.setSubFormTitle(wb, subFormCell3);
                                    subFormCell3.setCellValue(attrName);
                                    JSONArray subJsonArray3 = explainObject.getJSONObject("subForm").getJSONArray("items");
                                    for (Object subObject3 : subJsonArray3) {
                                        explainObject = (JSONObject) subObject3;
                                        attrName = explainObject.getString("attrName");
                                        subHeadRow3.createCell(subCellInt3++).setCellValue(attrName);
                                    }
                                    third.setEnd(subCellInt3 - 1);
                                    thirdList.add(third);
                                    subCellInt2 = subCellInt3;
                                } else {
                                    subHeadRow2.createCell(subCellInt2++).setCellValue(attrName);
                                    if ((Objects.equals(fieldType, FieldTypeEnum.COMBO.getType()) && Objects.equals(comboType, ComboTypeEnum.CUSTOM.getType()))|| Objects.equals(fieldType, FieldTypeEnum.STAR.getType()) || Objects.equals(fieldType, FieldTypeEnum.RADIO_GROUP.getType())) {
                                        JSONArray itemsArr = explainObject.getJSONArray("items");
                                        comboTextList = new ArrayList<>();
                                        for (int i = 0; i < itemsArr.size(); i++) {
                                            JSONObject itemsObj = itemsArr.getJSONObject(i);
                                            if (!Objects.equals(ShowTypeEnum.EXPORT_NO_IMPORT.getCode(), itemsObj.getInteger("showType"))) {
                                                comboTextList.add(itemsObj.getString("text"));
                                            }
                                        }
                                        options = comboTextList.toArray(new String[]{});
                                        if (options != null && options.length != 0) {
                                            // 隐藏sheet
                                            SXSSFSheet hideSheet = wb.createSheet("hiddenSheet" + j);
                                            // 创建名称，可被其他单元格引用
                                            Name category1Name = wb.createName();
                                            category1Name.setNameName("hidden" + j);
                                            category1Name.setRefersToFormula("hiddenSheet" + j + "!$A$1:$A$" + options.length);
                                            // 生成下拉列表
                                            generateDropDownList(sheet, subHeadRow, subHeadRow2, subCellInt2, options, hideSheet, j);
                                            // 隐藏该sheet
                                            wb.setSheetHidden(j, true);
                                            j++;
                                        }
                                    } else if (Objects.equals(fieldType, FieldTypeEnum.TEXTAREA.getType())) {
                                        sheet.setDefaultColumnStyle(subCellInt2 - 1, textStyle);
                                    }
                                }
                            }
                            second.setEnd(subCellInt2 - 1);
                            second.setImportPoJoList(thirdList);
                            secondList.add(second);
                            subCellInt = subCellInt2;
                        } else {
                            subHeadRow.createCell(subCellInt++).setCellValue(attrName);
                            if ((Objects.equals(fieldType, FieldTypeEnum.COMBO.getType()) && Objects.equals(comboType, ComboTypeEnum.CUSTOM.getType())) || Objects.equals(fieldType, FieldTypeEnum.STAR.getType()) || Objects.equals(fieldType, FieldTypeEnum.RADIO_GROUP.getType())) {
                                JSONArray itemsArr = explainObject.getJSONArray("items");
                                comboTextList = new ArrayList<>();
                                for (int i = 0; i < itemsArr.size(); i++) {
                                    JSONObject itemsObj = itemsArr.getJSONObject(i);
                                    if (!Objects.equals(ShowTypeEnum.EXPORT_NO_IMPORT.getCode(), itemsObj.getInteger("showType"))) {
                                        comboTextList.add(itemsObj.getString("text"));
                                    }
                                }
                                options = comboTextList.toArray(new String[]{});
                                if (options != null && options.length != 0) {
                                    // 隐藏sheet
                                    SXSSFSheet hideSheet = wb.createSheet("hiddenSheet" + j);
                                    // 创建名称，可被其他单元格引用
                                    Name category1Name = wb.createName();
                                    category1Name.setNameName("hidden" + j);
                                    category1Name.setRefersToFormula("hiddenSheet" + j +"!$A$1:$A$" + options.length);
                                    // 生成下拉列表
                                    generateDropDownList(sheet, headRow, subHeadRow, subCellInt, options, hideSheet, j);
                                    // 隐藏该sheet
                                    wb.setSheetHidden(j, true);
                                    j++;
                                }
                            } else if (Objects.equals(fieldType, FieldTypeEnum.TEXTAREA.getType())) {
                                sheet.setDefaultColumnStyle(subCellInt - 1, textStyle);
                            }
                        }
                    }
                    first.setEnd(subCellInt - 1);
                    first.setImportPoJoList(secondList);
                    importPoJoList.add(first);
                    cellInt = subCellInt;
                } else {
                    headRow.createCell(cellInt++).setCellValue(attrName);
                    if ((Objects.equals(fieldType, FieldTypeEnum.COMBO.getType()) && Objects.equals(comboType, ComboTypeEnum.CUSTOM.getType())) || Objects.equals(fieldType, FieldTypeEnum.STAR.getType())
                            || Objects.equals(fieldType, FieldTypeEnum.RADIO_GROUP.getType()) || Objects.equals(fieldType, FieldTypeEnum.STAGE_THRUSTER.getType())) {
                        JSONArray itemsArr = explainObject.getJSONArray("items");
                        comboTextList = new ArrayList<>();
                        if ( Objects.equals(businessType,XbbRefTypeEnum.PAYMENT.getCode()) && Objects.equals(explainObject.getString("attr"), PaymentEnum.STATUS.getAttr())){
                            for ( int i = 0; i < itemsArr.size(); i++ ) {
                                JSONObject itemsObj = itemsArr.getJSONObject(i);
                                if ( !Objects.equals(ShowTypeEnum.EXPORT_NO_IMPORT.getCode(), itemsObj.getInteger("showType"))
                                        && Objects.equals(itemsObj.getString("text"), PaymentStatusEnum.UN_RECEIVABLES.getAlias())
                                        && Objects.equals(excelType, DownloadExcelTypeEnum.TEMPLATE.getType()) ) {
                                    comboTextList.add(itemsObj.getString("text"));
                                }
                            }
                        } else if ( Objects.equals(businessType,XbbRefTypeEnum.PAY_PLAN.getCode()) && Objects.equals(explainObject.getString("attr"), PayPlanEnum.STATUS.getAttr())){
                            for ( int i = 0; i < itemsArr.size(); i++ ) {
                                JSONObject itemsObj = itemsArr.getJSONObject(i);
                                if ( !Objects.equals(ShowTypeEnum.EXPORT_NO_IMPORT.getCode(), itemsObj.getInteger("showType"))
                                        && Objects.equals(itemsObj.getString("text"), PayPlanStatusEnum.UN_RECEIVABLES.getAlias())
                                        && Objects.equals(excelType, DownloadExcelTypeEnum.TEMPLATE.getType()) ) {
                                    comboTextList.add(itemsObj.getString("text"));
                                }
                            }
                        } else{
                            for (int i = 0; i < itemsArr.size(); i++) {
                                JSONObject itemsObj = itemsArr.getJSONObject(i);
                                if (!Objects.equals(ShowTypeEnum.EXPORT_NO_IMPORT.getCode(), itemsObj.getInteger("showType"))) {
                                    comboTextList.add(itemsObj.getString("text"));
                                }
                            }
                        }
                        options = comboTextList.toArray(new String[]{});
                        if (options != null && options.length != 0) {
                            // 隐藏sheet
                            SXSSFSheet hideSheet = wb.createSheet("hiddenSheet" + j);
                            // 创建名称，可被其他单元格引用
                            Name category1Name = wb.createName();
                            category1Name.setNameName("hidden" + j);
                            category1Name.setRefersToFormula("hiddenSheet" + j + "!$A$1:$A$" + options.length);
                            // 生成下拉列表
                            generateDropDownList(sheet, headRow, subHeadRow, cellInt, options, hideSheet, j);
                            // 隐藏该sheet
                            wb.setSheetHidden(j, true);
                            j++;
                        }
                    } else if (Objects.equals(fieldType, FieldTypeEnum.TEXTAREA.getType())) {
                        sheet.setDefaultColumnStyle(cellInt - 1, textStyle);
                    }
                }
            }
            sheet.setDefaultColumnWidth(16);
            // 合并第一行
            if (cellInt > 1) {
                sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, cellInt - 1));
            }
            // 如果有子表单需要合并子表单单元格
            if (!importPoJoList.isEmpty()) {
                try {
                    ExcelUtil.mergeSubTitleCell(sheet, importPoJoList, 0, cellInt, 1, titleLength + 1);
                } catch (IllegalStateException e) {
                    e.printStackTrace();
                    LOG.error(e.getMessage());
                }
            }
            // 创建一个导入示例的sheet
            setImportInstructionsExample(wb,isFileImport);
            templateWorkBook = wb;

        } catch (Exception e) {
            LOG.error("ExcelOperatorService生成Excel出错", e);
        }
        return templateWorkBook;
    }

    private void generateDropDownList(SXSSFSheet sheet, SXSSFRow headRow, SXSSFRow subHeadRow, int cellInt, String[] options, SXSSFSheet hideSheet, int j) throws XbbException {
        if (options != null) {
            try {
                for (int i = 0; i < options.length; i++) {
                    hideSheet.createRow(i).createCell(0).setCellValue(options[i]);
                }

                // 生成下拉列表
                // 只对(0，0)单元格有效,下拉区域从excel填值行开始，即row.getRowNum() + 1
                DataValidationHelper dvHelper = sheet.getDataValidationHelper();
                Integer rowNum = headRow.getRowNum();
                if (Objects.nonNull(subHeadRow)) {
                    rowNum = subHeadRow.getRowNum();
                }
                CellRangeAddressList regions = new CellRangeAddressList(rowNum + 1, 65535, cellInt - 1, cellInt - 1);
                // 生成下拉框内容
                DataValidationConstraint dvConstraint = dvHelper.createFormulaListConstraint("hidden" + j);
                // 绑定下拉框和作用区域
                DataValidation dataValidation = dvHelper.createValidation(dvConstraint, regions);
                if (dataValidation instanceof XSSFDataValidation) {
                    //数据校验
                    dataValidation.setSuppressDropDownArrow(true);
                    dataValidation.setShowErrorBox(true);
                } else {
                    dataValidation.setSuppressDropDownArrow(false);
                }
                // 对sheet页生效
                sheet.addValidationData(dataValidation);
            } catch (Exception e) {
                throw new XbbException(SystemErrorCodeEnum.API_ERROR_100001);
            }
        }
    }

    @Override
    public Workbook createExcel(List<PaasFormDataEntityExt> formDataList, JSONArray explainJsonArray, String name, FormDataListDTO formDataListDTO) {
        // 导出状态
        int totalRowNum = formDataList.size();
        int totalRowCount = 0;
        for (PaasFormDataEntityExt entityExt : formDataList) {
            totalRowCount += FastJsonHelper.getDepthJsonObjectForExport(entityExt.getData());
        }
        // 创建标题头部和行标题
        Workbook workbook = createExcelTemplate(formDataListDTO.getBusinessType(),explainJsonArray, name,DownloadExcelTypeEnum.TEMPLATE.getType(), totalRowCount, false);
        // 填充数据到Excel
        fillDataIntoExcel(workbook, formDataListDTO, totalRowNum, formDataList, explainJsonArray);
        return workbook;
    }


    /**
     *
     * @Description: 填充数据到excel
     * @param workbook
     * @param formDataListDTO
     * @param totalRowNum
     * @param formDataList
     * @param explainJsonArray
     * @return: void
     * @Author: shen.yang
     * @Date: 2022/9/9  9:47
     */
    private void fillDataIntoExcel(Workbook workbook, FormDataListDTO formDataListDTO, int totalRowNum, List<PaasFormDataEntityExt> formDataList, JSONArray explainJsonArray ) {
        SXSSFSheet sheet = (SXSSFSheet) workbook.getSheetAt(0);

        CellStyle cellStyle = workbook.createCellStyle();
        DataFormat format = workbook.createDataFormat();
        cellStyle.setDataFormat(format.getFormat("@"));

        // 记录Excel的行数
        int rowIntNum = sheet.getLastRowNum() + 1;
        // 存放需填充数据的对象
        List<ImportPoJo> importPoJoList = new ArrayList<>(1 << 5);
        //部分导出是否合并单元格
        boolean mergeCellsFlag = null!=formDataListDTO.getExcelPartialExportPojo()
                && Objects.equals(formDataListDTO.getExcelPartialExportPojo().getPartialExport(),1)
                && Objects.equals(formDataListDTO.getExcelPartialExportPojo().getMergeCells(),0);
        boolean isBigData = totalRowNum > 10000 || mergeCellsFlag;
        for (int i = 0; i < totalRowNum; i++) {
            Map<String, ImportPoJo> subAttrMap = importPoJoList.stream().collect(Collectors.toMap(ImportPoJo::getAttr, v -> v));
            JSONObject dataJsonObject = formDataList.get(i).getData();
            if (dataJsonObject == null) {
                continue;
            }
            // 创建数据行
            SXSSFRow row = sheet.createRow(rowIntNum);
            int cellInt = 0;
            // 遍历字段，只处理普通字段的数据，子表单先只占用cell数，后续再赋值（主要是因为供应商有产品子表单会超过500行）
            for (Object object : explainJsonArray) {
                JSONObject jsonObject = (JSONObject) object;
                Integer fieldType = jsonObject.getInteger("fieldType");
                String attr = jsonObject.getString("attr");
                // 根据字段信息获取数据
                if (Objects.equals(fieldType, FieldTypeEnum.SUB_FORM.getType())) {
                    //子表单类型的字段，先只占用列数，最后再处理数据
                    JSONArray subJsonArray = jsonObject.getJSONObject("subForm").getJSONArray("items");
                    ImportPoJo importPoJo;
                    //防止重复插入
                    if(i == 0 && !subAttrMap.keySet().contains(attr)){
                        importPoJo = new ImportPoJo();
                        importPoJo.setStart(cellInt);
                        importPoJo.setAttr(attr);
                        importPoJo.setSubJSONArray(subJsonArray);
                        importPoJoList.add(importPoJo);
                    }else{
                        importPoJo = subAttrMap.get(attr);
                    }
                    for (Object subObject : subJsonArray) {
                        JSONObject subJsonObject = (JSONObject) subObject;
                        fieldType = subJsonObject.getInteger("fieldType");
                        attr = subJsonObject.getString("attr");
                        if(Objects.equals(fieldType, FieldTypeEnum.SUB_FORM.getType())){
                            List<ImportPoJo> importPoJoList1 = importPoJo.getImportPoJoList();
                            if(CollectionsUtil.isEmpty(importPoJoList1)){
                                importPoJoList1 = new ArrayList<>();
                                importPoJo.setImportPoJoList(importPoJoList1);
                            }
                            ImportPoJo importPoJo1 = new ImportPoJo();
                            importPoJo1.setStart(cellInt);
                            importPoJo1.setEnd(cellInt - 1);
                            importPoJo1.setAttr(attr);
                            //二级子表单处理
                            JSONArray subJsonArray2 = subJsonObject.getJSONObject("subForm").getJSONArray("items");
                            for (Object subObject1 : subJsonArray2) {
                                JSONObject subJsonObject1 = (JSONObject) subObject1;
                                fieldType = subJsonObject1.getInteger("fieldType");
                                attr = subJsonObject1.getString("attr");
                                if(Objects.equals(fieldType, FieldTypeEnum.SUB_FORM.getType())){
                                    List<ImportPoJo> importPoJoList2 = importPoJo1.getImportPoJoList();
                                    if(CollectionsUtil.isEmpty(importPoJoList2)){
                                        importPoJoList2 = new ArrayList<>();
                                        importPoJo1.setImportPoJoList(importPoJoList2);
                                    }
                                    JSONArray subJsonArray3 = subJsonObject1.getJSONObject("subForm").getJSONArray("items");
                                    //三级子表单处理
                                    ImportPoJo importPoJo2 = new ImportPoJo();
                                    importPoJo2.setStart(cellInt);
                                    cellInt += subJsonArray3.size();
                                    importPoJo2.setEnd(cellInt - 1);
                                    importPoJo2.setAttr(attr);
                                    importPoJo2.setSubJSONArray(subJsonArray3);
                                    if(i == 0){
                                        importPoJoList2.add(importPoJo2);
                                    }
                                }else{
                                    cellInt ++;
                                }
                            }
                            importPoJo1.setEnd(cellInt - 1);
                            importPoJo1.setSubJSONArray(subJsonArray2);
                            if(i == 0){
                                importPoJoList1.add(importPoJo1);
                            }
                        }else{
                            cellInt ++;
                        }
                    }
                    importPoJo.setEnd(cellInt - 1);
                } else if (Objects.equals(fieldType, FieldTypeEnum.IMAGE.getType())) {
                    // 图片特殊解析
                    Cell dataCell = row.createCell(cellInt++);
                    dataCell.setCellStyle(cellStyle);
                    String valueString = Objects.toString(dataJsonObject.getString(attr), "[]");
                    String imageString = fileImportExportHelp.getImageStringByImageInfo(valueString);
                    dataCell.setCellValue(imageString);
                } else if (Objects.equals(fieldType, FieldTypeEnum.UPLOAD.getType())) {
                    // 附件特殊解析
                    Cell dataCell = row.createCell(cellInt++);
                    dataCell.setCellStyle(cellStyle);
                    String valueString = Objects.toString(dataJsonObject.getString(attr), "[]");
                    String imageString = fileImportExportHelp.getFileStringByFileInfo(valueString);
                    dataCell.setCellValue(imageString);
                } else {
                    // 普通字段的逻辑
                    Cell dataCell = row.createCell(cellInt++);
                    dataCell.setCellStyle(cellStyle);
                    String valueString = Objects.toString(dataJsonObject.getString(attr), "");
                    valueString = number2String(valueString, fieldType);
                    dataCell.setCellValue(valueString);
                }
            }

            //处理子表单
            //用于需要填充的时候计数已经填充的最大行数，防止多余的填充操作
            int fillRowNum = 0;
            List<Integer> subColumns = getSubColumns(importPoJoList);
            //记录cell为空的列，fill的时候可以不做填充，节约性能
            List<Integer> enptyCellList = new ArrayList<>();
            for (int j = 0; j < cellInt; j++) {
                if(!subColumns.contains(j)){
                    SXSSFCell cell = row.getCell(j);
                    if(Objects.nonNull(cell)){
                        String stringCellValue = cell.getStringCellValue();
                        if(StringUtil.isEmpty(stringCellValue)){
                            enptyCellList.add(j);
                        }
                    }else{
                        enptyCellList.add(j);
                    }
                }

            }
            List<ImportPoJo> copyImportList = new ArrayList<>(importPoJoList);
            //供应商的产品特殊处理一下，放在最后处理，防止先处理产品导致行数超过500
            if(Objects.equals(formDataListDTO.getBusinessType(), XbbRefTypeEnum.SUPPLIER.getCode())){
                List<ImportPoJo> collect = copyImportList.stream().filter(v -> Objects.equals(v.getAttr(), SupplierEnum.PRODUCT_LIST.getAttr())).collect(Collectors.toList());
                if(CollectionsUtil.isNotEmpty(collect)){
                    ImportPoJo importPoJo = collect.get(0);
                    copyImportList.removeIf(importPoJo :: equals);
                    copyImportList.add(importPoJo);
                }
            }



            //一级子表单最大长度，用于最外层的合并行数据
            int maxSubNum = 1;
            for (ImportPoJo importPoJo : copyImportList) {
                String attr = importPoJo.getAttr();
                JSONArray subJSONArray = importPoJo.getSubJSONArray();
                JSONArray subDataJsonArray = dataJsonObject.getJSONArray(attr);
                Integer start = importPoJo.getStart();
                int addRowNum = 0;
                if(CollectionsUtil.isNotEmpty(subDataJsonArray)){
                    List<ImportPoJo> subImportPojo = importPoJo.getImportPoJoList();
                    Map<String, ImportPoJo> subImportPojoMap = new HashMap<>();
                    if(CollectionsUtil.isNotEmpty(subImportPojo)){
                        subImportPojoMap = subImportPojo.stream().collect(Collectors.toMap(ImportPoJo::getAttr, v -> v));
                    }
                    for (Object object : subDataJsonArray) {
                        //记录当前一级子表单里面的二级子表单最大长度
                        int maxSubLength = 1;
                        JSONObject dataJSON = (JSONObject) object;
                        // 生成子单元格数据
                        int rowNum = rowIntNum + addRowNum;
                        SXSSFRow subRow = sheet.getRow(rowNum);
                        int subStart = start;
                        int subSize = subJSONArray.size();
                        for (int k = 0; k < subSize; k++) {
                            JSONObject jsonObject = (JSONObject)subJSONArray.get(k);
                            if(!Objects.equals(jsonObject.get("isOpen"), 1) || !Objects.equals(jsonObject.get("visible"), 1)){
                                //未启用字段和不可见字段过滤
                                continue;
                            }
                            attr = jsonObject.getString("attr");
                            Integer fieldType = jsonObject.getInteger("fieldType");
                            if(Objects.equals(FieldTypeEnum.SUB_FORM.getType(), fieldType)){
                                //二级子表单
                                JSONArray subDataJsonArray1 = dataJSON.getJSONArray(attr);
                                ImportPoJo importPoJo1 = subImportPojoMap.get(attr);
                                List<ImportPoJo> subImportPojo1 = importPoJo1.getImportPoJoList();
                                Map<String, ImportPoJo> subImportPojoMap2 = new HashMap<>();
                                if(CollectionsUtil.isNotEmpty(subImportPojo1)){
                                    subImportPojoMap2 = subImportPojo1.stream().collect(Collectors.toMap(ImportPoJo::getAttr, v -> v));
                                }
                                if(CollectionsUtil.isNotEmpty(subDataJsonArray1)){
                                    maxSubLength = Math.max(subDataJsonArray1.size(), maxSubLength);
                                    int addSubRow = 0;
                                    for (Object object1 : subDataJsonArray1) {
                                        JSONObject subDataObject =  (JSONObject)object1;
                                        // 生成二级子表单的单元格数据
                                        int subSize1 = importPoJo1.getSubJSONArray().size();
                                        int sub3Start = importPoJo1.getStart();
                                        for (int m = 0; m < subSize1; m++) {
                                            JSONObject subJsonObject = (JSONObject)importPoJo1.getSubJSONArray().get(m);
                                            if(!Objects.equals(subJsonObject.get("isOpen"), 1) || !Objects.equals(subJsonObject.get("visible"), 1)){
                                                //未启用字段和不可见字段过滤
                                                continue;
                                            }
                                            String subAttr = subJsonObject.getString("attr");
                                            fieldType = subJsonObject.getInteger("fieldType");
                                            if(Objects.equals(FieldTypeEnum.SUB_FORM.getType(), fieldType)){
                                                //三级子表单暂时不处理
                                                JSONArray subDataJsonArray2 = subDataObject.getJSONArray(subAttr);
                                                ImportPoJo importPoJo2 = subImportPojoMap2.get(subAttr);
                                                JSONArray subJSONArray1 = importPoJo2.getSubJSONArray();
                                                if(CollectionsUtil.isNotEmpty(subDataJsonArray2)){
                                                    JSONObject subData = (JSONObject)subDataJsonArray2.get(0);
                                                    int subSize2 = subJSONArray1.size();
                                                    subRow = sheet.getRow(rowIntNum + addRowNum + addSubRow );
                                                    for (int n = 0; n < subSize2; n++) {
                                                        JSONObject subJsonObject1 = (JSONObject)importPoJo2.getSubJSONArray().get(n);
                                                        String subAttr1 = subJsonObject1.getString("attr");
                                                        if(Objects.isNull(subRow)){
                                                            int createRowNum =  rowIntNum + addRowNum + addSubRow;
                                                            subRow = sheet.createRow(createRowNum);
                                                            //新建行还要判断是否填充
                                                            if (isBigData && createRowNum >= fillRowNum) {
                                                                for (int j = 0; j < cellInt; j++) {
                                                                    if(!subColumns.contains(j)){
                                                                        SXSSFCell cell = subRow.createCell(j);
                                                                        cell.setCellValue(row.getCell(j).getStringCellValue());
                                                                    }
                                                                }
                                                                fillRowNum = createRowNum;
                                                            }
                                                        }
                                                        subRow.createCell(importPoJo2.getStart() + n).setCellValue(subData.getString(subAttr1));
                                                    }
                                                }

                                                sub3Start += importPoJo2.getEnd() - importPoJo2.getStart();
                                            }else{
                                                subRow = sheet.getRow(rowIntNum + addRowNum + addSubRow );
                                                if(Objects.isNull(subRow)){
                                                    int createRowNum =  rowIntNum + addRowNum + addSubRow;
                                                    subRow = sheet.createRow(createRowNum);
                                                    //新建行还要判断是否填充
                                                    if (isBigData && createRowNum >= fillRowNum) {
                                                        for (int j = 0; j < cellInt; j++) {
                                                            if(!subColumns.contains(j)){
                                                                SXSSFCell cell = subRow.createCell(j);
                                                                cell.setCellValue(row.getCell(j).getStringCellValue());
                                                            }
                                                        }
                                                        fillRowNum = createRowNum;
                                                    }
                                                }
                                                subRow.createCell(sub3Start + m).setCellValue(subDataObject.getString(subAttr));
                                            }
                                        }
                                        //判断是否填充
                                        if (isBigData && rowNum >= fillRowNum && addRowNum > 0) {
                                            for (int j = 0; j < cellInt; j++) {
                                                if(!subColumns.contains(j) && !enptyCellList.contains(j)){
                                                    subRow.createCell(j).setCellValue(row.getCell(j).getStringCellValue());
                                                    fillRowNum = rowNum;
                                                }
                                            }
                                        }
                                        addSubRow ++;
                                    }
                                }
                                subStart = subStart + (importPoJo1.getEnd() - importPoJo1.getStart());
                            }else{
                                if(Objects.isNull(subRow)){
                                    subRow = sheet.createRow(rowNum);
                                }
                                // 导出的时候，如果子表单为图片或者附件，则重新解析下
                                String valueString;
                                if (Objects.equals(fieldType, FieldTypeEnum.IMAGE.getType())) {
                                    valueString = fileImportExportHelp.getImageStringByImageInfo(dataJSON.getString(attr));
                                } else if (Objects.equals(fieldType, FieldTypeEnum.UPLOAD.getType())) {
                                    valueString = fileImportExportHelp.getFileStringByFileInfo(dataJSON.getString(attr));
                                } else {
                                    valueString = dataJSON.getString(attr);
                                }
                                subRow.createCell(subStart + k).setCellValue(valueString);
                            }
                        }
                        //判断是否填充
                        if (isBigData && rowNum >= fillRowNum && addRowNum > 0) {
                            for (int j = 0; j < cellInt; j++) {
                                if(!subColumns.contains(j) && !enptyCellList.contains(j)){
                                    subRow.createCell(j).setCellValue(row.getCell(j).getStringCellValue());
                                    fillRowNum = rowNum;
                                }
                            }
                        }
                        addRowNum += maxSubLength;
                    }
                    maxSubNum = Math.max(maxSubNum, addRowNum);
                }
            }

            //合并单元格处理
            if (!importPoJoList.isEmpty() && maxSubNum > 1) {
                if (!isBigData) {
                    ExcelUtil.mergeDataCell(sheet, importPoJoList, cellInt, rowIntNum, rowIntNum + maxSubNum - 1);
                }
                rowIntNum = maxSubNum + rowIntNum;
            }else{
                rowIntNum++;
            }
        }
    }

    /**
     *
     * @Description: 获取子表单在sheet中的列数的集合，用于填充时候列的判断
     * @param importPoJoList
     * @return: java.util.List<java.lang.Integer>
     * @Author: shen.yang
     * @Date: 2022/9/7  10:07
     */
    private List<Integer> getSubColumns(List<ImportPoJo> importPoJoList){
        List<Integer> subColumns = new ArrayList<>();
        if(CollectionsUtil.isNotEmpty(importPoJoList)){
            for (ImportPoJo importPoJo : importPoJoList) {
                Integer start = importPoJo.getStart();
                Integer end = importPoJo.getEnd();
                for (int i = start; i <= end; i++) {
                    subColumns.add(i);
                }
            }
        }
        return subColumns;
    }

    @Override
    public Workbook createEmptyExcel4MoreTitle(int width, String sheetName, String[] colTitle, List<List<Map<String, Object>>> moreTitleList) {
        Workbook wb = new SXSSFWorkbook();
        Sheet sheet = wb.createSheet(sheetName);
        Row row;
        Cell cell;
        // 创建单元格，并设置值表头 设置表头居中
        CellStyle titleStyle = wb.createCellStyle();
        CellStyle selectStyle = wb.createCellStyle();
        for (int i = 0; i < colTitle.length; i++) {
            if (i == 0) {
                sheet.setColumnWidth((short) i, 20 * 256);
            } else {
                sheet.setColumnWidth((short) i, width * 256);
            }
        }
        sheet.setDefaultRowHeight((short) 300);
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        titleStyle.setAlignment(HorizontalAlignment.LEFT);
        selectStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        selectStyle.setAlignment(HorizontalAlignment.LEFT);
        selectStyle.setBorderBottom(BorderStyle.THIN);
        selectStyle.setBorderLeft(BorderStyle.THIN);
        selectStyle.setBorderTop(BorderStyle.THIN);
        selectStyle.setBorderRight(BorderStyle.THIN);
        // 设置标题字体格式
        Font fontTitle = wb.createFont();
        Font font = wb.createFont();
        // 设置字体样式
        fontTitle.setFontHeightInPoints((short) 20);
        fontTitle.setFontName(ImportConstant.FONT_COURIER_NEW);
        font.setFontHeightInPoints((short) 10);
        font.setFontName(ImportConstant.FONT_COURIER_NEW);
        titleStyle.setFont(fontTitle);
        selectStyle.setFont(font);

        titleStyle.setWrapText(true);
        selectStyle.setWrapText(true);

        titleStyle.setWrapText(true);
        selectStyle.setWrapText(true);
        int lastRow = 0;
        if (moreTitleList != null && moreTitleList.size() > 0) {
            for (List<Map<String, Object>> titleList : moreTitleList) {
                row = sheet.createRow(lastRow);
                int columnTemp = 0;
                for (Map<String, Object> map : titleList) {
                    int columnCount = (Integer) map.get("columnCount");
                    int rowCount = lastRow;
                    if (map.containsKey("rowCount")) {
                        rowCount += (Integer) map.get("rowCount") - 1;
                    }

                    String title = (String) map.get("title");
                    cell = row.createCell((short) columnTemp);
                    sheet.addMergedRegion(new CellRangeAddress(lastRow, rowCount, columnTemp, columnTemp + columnCount - 1));
                    cell.setCellStyle(titleStyle);
                    cell.setCellValue(title);
                    columnTemp += columnCount;
                }
                lastRow++;//加一行
            }
        }
        // 在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
        //第一行  大标题
        if (moreTitleList != null && moreTitleList.size() > 0) {
            row = sheet.createRow(lastRow);
        } else {
            row = sheet.createRow(0);
        }
        for (int i = 0; i < colTitle.length; i++) {
            cell = row.createCell((short) i);
            cell.setCellStyle(selectStyle);

            cell.setCellValue(colTitle[i]);
        }
        return wb;
    }

    @Override
    public SXSSFWorkbook createStatementExcelWithHead(String sheetName, List<List<List<Object>>> titleList, int level) {
        SXSSFWorkbook wb = new SXSSFWorkbook();
        SXSSFSheet sheet = wb.createSheet(sheetName);
        sheet.setDefaultRowHeight((short) 300);
        // 创建单元格，并设置值表头 设置表头居中
        CellStyle titleStyle = wb.createCellStyle();
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        titleStyle.setAlignment(HorizontalAlignment.CENTER);

        // 填充表头字段名
        if (CollectionsUtil.isNotEmpty(titleList)) {
            if (level == 1) {
                SXSSFRow row = sheet.createRow(0);
                SXSSFCell cell;
                int columnTemp = 0;
                for (List<List<Object>> title : titleList) {
                    cell = row.createCell(columnTemp);
                    cell.setCellStyle(titleStyle);
                    cell.setCellValue((String) title.get(0).get(0));
                    columnTemp++;
                }
            } else if (level == 2) {
                // 包含关联产品字段
                SXSSFRow row0 = sheet.createRow(0);
                SXSSFRow row1 = sheet.createRow(1);
                SXSSFCell cell0;
                SXSSFCell cell1;
                int columnTemp = 0;
                for (List<List<Object>> title : titleList) {
                    if (title.size() == 1) {
                        cell0 = row0.createCell(columnTemp);
                        cell0.setCellStyle(titleStyle);
                        cell0.setCellValue((String) title.get(0).get(0));
                        row1.createCell(columnTemp);
                        CellRangeAddress region = new CellRangeAddress(0, 1, columnTemp, columnTemp);
                        sheet.addMergedRegion(region);
                        columnTemp++;
                    } else {
                        // 关联产品字段处理
                        int index = columnTemp;
                        cell0 = row0.createCell(columnTemp);
                        cell0.setCellStyle(titleStyle);
                        cell0.setCellValue((String) title.get(0).get(0));
                        List<Object> objects = title.get(1);
                        for (Object value : objects) {
                            cell1 = row1.createCell(columnTemp);
                            cell1.setCellStyle(titleStyle);
                            cell1.setCellValue((String) value);
                            columnTemp++;
                        }
                        CellRangeAddress region = new CellRangeAddress(0, 0, index, columnTemp - 1);
                        sheet.addMergedRegion(region);
                    }
                }
            }
        }
        return wb;
    }

    @Override
    public Workbook createExcel(Workbook workbook, List<PaasFormDataEntityExt> formDataList, JSONArray explainJsonArray, String name, FormDataListDTO formDataListDTO) throws XbbException {

        if (workbook == null) {
            workbook = createExcelTemplate(formDataListDTO.getBusinessType(),explainJsonArray, name,DownloadExcelTypeEnum.TEMPLATE.getType(), formDataList.size(), false);
        }
        // 填充数据到Excel
        fillDataIntoExcel(workbook, formDataListDTO, formDataList.size(), formDataList, explainJsonArray);
        return workbook;
    }

    /**
     * 格式化科学计数法
     * @param value 字段值
     * @param fieldType 字段类型
     * @return
     * @throws
     * @author hongxiao
     * @date 2019-07-03 15:39
     * @since v1.0
     * @version v1.0
     */
    private String number2String(String value, Integer fieldType) {
        String valueStr = value;
        if(Objects.equals(FieldTypeEnum.NUM.getType(), fieldType) && RegexUtil.isNumber(value)) {
            BigDecimal bd = new BigDecimal(value);
            valueStr = bd.toPlainString();
        }
        return valueStr;
    }

    /**
     * 创建一个导入字段示例的sheet
     * @param wb
     */
    private void setImportInstructionsExample(SXSSFWorkbook wb, boolean isFileImport) {
        Font font = wb.createFont();
        font.setColor(IndexedColors.GREY_50_PERCENT.getIndex());
        wb.setSheetName(0, I18nMessageUtil.getMessage(StringConstant.IMPORT_DOCUMENT));
        SXSSFSheet sheet = wb.createSheet(I18nMessageUtil.getMessage(StringConstant.IMPORT_NOTES_AND_EXAMPLES));
        for (ImportExampleNameEnum importExampleNameEnum : ImportExampleNameEnum.values()) {
            int rowLine = importExampleNameEnum.getRow();
            SXSSFRow row = sheet.createRow(rowLine);

            CellStyle cellStyle = wb.createCellStyle();
            // 垂直
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            // 水平
            cellStyle.setAlignment(HorizontalAlignment.LEFT);
            cellStyle.setWrapText(true);
            if (rowLine == 0) {
                cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
                cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            }
            if (rowLine == 9 || rowLine == 10) {
                cellStyle.setFont(font);
            }
            //第一列
            SXSSFCell cell1 = row.createCell(0);
            cell1.setCellValue(importExampleNameEnum.getFirstCol());
            cell1.setCellStyle(cellStyle);

            if (rowLine == 9 || rowLine == 10) {
                font.setColor(IndexedColors.BLACK.getIndex());
                cellStyle.setFont(font);
            }

            //第二列
            SXSSFCell cell2 = row.createCell(1);
            cell2.setCellValue(importExampleNameEnum.getSecondCol());
            cell2.setCellStyle(cellStyle);

            //第三列
            SXSSFCell cell3 = row.createCell(2);
            if (rowLine == 11) {
                cell3.setCellValue(String.format(importExampleNameEnum.getThirdCol(), ImportConstant.MAX_LINK_DATA_NUM));
            }else {
                cell3.setCellValue(importExampleNameEnum.getThirdCol());
            }
            if (Objects.equals(rowLine, BasicConstant.NINE) && Objects.equals(true, isFileImport)) {
                cell2.setCellValue(I18nMessageUtil.getMessage(StringConstant.EXPORT_IMAGE_EXAMPLE));
                cell3.setCellValue(I18nMessageUtil.getMessage(StringConstant.EXPORT_IMAGE_RULE));
            } else if (Objects.equals(rowLine, BasicConstant.TEN) && Objects.equals(true, isFileImport)) {
                cell2.setCellValue(I18nMessageUtil.getMessage(StringConstant.EXPORT_FILE_EXAMPLE));
                cell3.setCellValue(I18nMessageUtil.getMessage(StringConstant.EXPORT_FILE_RULE));
            }
            cell3.setCellStyle(cellStyle);
        }
        sheet.setColumnWidth(0, 20 * 256);
        sheet.setColumnWidth(1, 40 * 256);
        sheet.setColumnWidth(2, 40 * 256);
        sheet.addMergedRegion(new CellRangeAddress(12, 12, 0, 2));
    }
}